import copy
from rest_framework import viewsets, permissions, mixins, filters
from rest_framework.generics import GenericAPIView
from api.serializers import *
from api.filter import *
from rest_framework.pagination import PageNumberPagination
from django_filters.rest_framework import DjangoFilterBackend
from django.db.models.functions import Lower
from functools import reduce
import operator, six
from rest_framework.response import Response
import re
from ast import literal_eval
from django.conf import settings
import os
import uuid
import xlsxwriter
from projs.views import download_response


class CustomDjangoModelPermission(permissions.DjangoModelPermissions):
    def __init__(self):
        self.perms_map = copy.deepcopy(self.perms_map)  # you need deepcopy when you inherit a dictionary type
        self.perms_map['GET'] = ['%(app_label)s.view_%(model_name)s']


class SearchFilterMixins(filters.SearchFilter):
    def custom_filter_queryset(self, request, queryset, view, *args, **kwargs):
        search_fields_ = getattr(view, 'search_fields', None)
        search_terms = self.get_search_terms(request)
        search_fields = []
        for search_field in search_fields_:
            if search_field in kwargs:
                search_fields.append(kwargs[search_field])
            else:
                search_fields.append(search_field)
        if not search_fields or not search_terms:
            return queryset
        orm_lookups = [
            self.construct_search(six.text_type(search_field))
            for search_field in search_fields
        ]
        conditions = []
        for search_term in search_terms:
            queries = [
                models.Q(**{orm_lookup: search_term})
                for orm_lookup in orm_lookups
            ]
            conditions.append(reduce(operator.or_, queries))
        return queryset.filter(reduce(operator.or_, conditions)).distinct()


class OrderingFilterMixins(filters.OrderingFilter):
    def custom_filter_queryset(self, request, queryset, view, *args, **kwargs):
        ordering = self.get_ordering(request, queryset, view)
        if ordering is not None:
            for o in ordering:
                if o.startswith('-'):
                    n = o[1:]
                    if n in kwargs.keys():
                        n = n.replace(n, kwargs[n])
                    queryset = queryset.order_by(Lower(n)).reverse()
                else:
                    if o in kwargs.keys():
                        o = o.replace(o, kwargs[o])
                    queryset = queryset.order_by(Lower(o))
        return queryset


class InventoryViewSet(viewsets.ModelViewSet):
    queryset = AnsibleInventory.objects.all().order_by('id')
    serializer_class = InventorySerializer
    permission_classes = (CustomDjangoModelPermission,)


class AssetsPagination(PageNumberPagination):
    page_size = 10
    page_size_query_param = 'page_size'
    page_query_param = "page"
    max_page_size = 100

class AssetsViewSet(viewsets.ModelViewSet):
    queryset = Assets.objects.all().order_by('id')
    serializer_class = AssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        asset_management_ip = instance.asset_management_ip
        self.perform_destroy(instance)
        if Assets.objects.filter(asset_management_ip=asset_management_ip).count() == 0:
            FortServer.objects.filter(server=asset_management_ip).delete()
        return Response(status=204)


class AssetsListSearchFilter(SearchFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'asset_management_ip': 'asset_management_ip__IP', 'asset_product': 'asset_product__product_name'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class AssetsListOrderingFilter(OrderingFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'asset_management_ip': 'asset_management_ip__IP', 'is_expired': 'asset_expire_day', 'asset_product': 'asset_product__product_name'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class AssetsListViewSet(mixins.ListModelMixin, viewsets.GenericViewSet):
    queryset = Assets.objects.all().order_by('id')
    serializer_class = AssetsListSerializer
    pagination_class = AssetsPagination
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, AssetsListSearchFilter, AssetsListOrderingFilter)
    filter_class = AssetsFilter
    search_fields = ('asset_nu', 'asset_management_ip', 'asset_product')
    ordering_fields = ('id', 'asset_type', 'asset_nu', 'asset_management_ip', 'asset_status', 'is_expired', 'asset_product')


class AssetsUpdateMixin(viewsets.ModelViewSet):
    def update(self, request, *args, **kwargs):
        return super().update(request, partial=True)


class ServerAssetsViewSet(AssetsUpdateMixin):
    queryset = ServerAssets.objects.all().order_by('id')
    serializer_class = ServerAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class NetworkAssetsViewSet(AssetsUpdateMixin):
    queryset = NetworkAssets.objects.all().order_by('id')
    serializer_class = NetworkAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class OfficeAssetsViewSet(AssetsUpdateMixin):
    queryset = OfficeAssets.objects.all().order_by('id')
    serializer_class = OfficeAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class SecurityAssetsViewSet(AssetsUpdateMixin):
    queryset = SecurityAssets.objects.all().order_by('id')
    serializer_class = SecurityAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class StorageAssetsViewSet(AssetsUpdateMixin):
    queryset = StorageAssets.objects.all().order_by('id')
    serializer_class = StorageAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class SoftwareAssetsViewSet(AssetsUpdateMixin):
    queryset = SoftwareAssets.objects.all().order_by('id')
    serializer_class = SoftwareAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class AssetProviderViewSet(viewsets.ModelViewSet):
    queryset = AssetProvider.objects.all().order_by('id')
    serializer_class = AssetProviderSerializer
    permission_classes = (CustomDjangoModelPermission,)


class IDCViewSet(viewsets.ModelViewSet):
    queryset = IDC.objects.all().order_by('id')
    serializer_class = IDCSerializer
    permission_classes = (CustomDjangoModelPermission,)


class CabinetViewSet(viewsets.ModelViewSet):
    queryset = Cabinet.objects.all().order_by('id')
    serializer_class = CabinetSerializer
    permission_classes = (CustomDjangoModelPermission,)


class FortViewSet(viewsets.ModelViewSet):
    queryset = FortServer.objects.all().order_by('id')
    serializer_class = FortSerializer
    permission_classes = (CustomDjangoModelPermission,)


class FortListPagination(PageNumberPagination):
    page_size = 10
    page_size_query_param = 'page_size'
    page_query_param = "page"
    max_page_size = 100

class FortListOrderingFilter(OrderingFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'IP': 'server__IP'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class FortListSearchFilter(SearchFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'IP': 'server__IP', 'asset_nu': 'server__asset_management_ip__asset_nu'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class FortListViewSet(mixins.ListModelMixin, viewsets.GenericViewSet):
    queryset = FortServer.objects.all().order_by('id')
    serializer_class = FortListSerializer
    pagination_class = FortListPagination
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, FortListSearchFilter, FortListOrderingFilter)
    filter_class = FortListFilter
    search_fields = ('asset_nu', 'IP')
    ordering_fields = ('id', 'IP','server_status','server_protocol')


class FortUserViewSet(viewsets.ModelViewSet):
    queryset = FortServerUser.objects.all().order_by('id')
    serializer_class = FortUserSerializer
    permission_classes = (CustomDjangoModelPermission,)

class FortUserListPagination(PageNumberPagination):
    page_size = 10
    page_size_query_param = 'page_size'
    page_query_param = "page"
    max_page_size = 100

class FortUserListOrderingFilter(OrderingFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'IP': 'fort_server__server__IP', 'server_protocol': 'fort_server__server_protocol'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class FortUserListSearchFilter(SearchFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'IP': 'fort_server__server__IP', 'asset_nu': 'fort_server__server__asset_management_ip__asset_nu'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class FortUserListViewSet(mixins.ListModelMixin, viewsets.GenericViewSet):
    queryset = FortServerUser.objects.filter(fort_user_status=1, fort_server__server_status=1).order_by('id')
    serializer_class = FortUserListSerializer
    pagination_class = FortUserListPagination
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, FortUserListSearchFilter, FortUserListOrderingFilter)
    filter_class = FortUserListFilter
    search_fields = ('IP', 'asset_nu')
    ordering_fields = ('id', 'IP', 'server_protocol', 'fort_username')


class PeriodicTaskViewSet(viewsets.ModelViewSet):
    queryset = PeriodicTask.objects.all()
    serializer_class = PeriodicTaskSerializer
    permission_classes = (CustomDjangoModelPermission,)

    def convert_to_dict(self, data):
        if isinstance(data, dict):
            output = '{'
            i = 0
            for k, v in data.items():
                if i > 0:
                    output += ', '
                if type(k) == str:
                    output += '"' + k + '": '
                else:
                    output += str(k) + ': '
                if type(v) == str:
                    output += '"' + v + '"'
                elif type(v) in [int, float]:
                    output += str(v)
                else:
                    output += self.convert_to_dict(v)
                i += 1
            output += '}'
        elif isinstance(data, list):
            output = '['
            i = 0
            for v in data:
                if i > 0:
                    output += ', '
                if type(v) == str:
                    output += '"' + v + '"'
                elif type(v) in [int, float]:
                    output += str(v)
                else:
                    output += self.convert_to_dict(v)
                i += 1
            output += ']'
        return output

    def create(self, request, *args, **kwargs):
        try:
            if request.data.get('args', False):
                request.data['args'] = self.convert_to_dict(literal_eval(request.data.get('args')))
            if request.data.get('kwargs', False):
                request.data['kwargs'] = self.convert_to_dict(literal_eval(request.data.get('kwargs')))
        except:
            return Response(data={'msg': '“参数”格式错误，请重新输入！'}, status=403)
        return super().create(request, *args, **kwargs)

    def update(self, request, *args, **kwargs):
        try:
            if request.data.get('args', False):
                request.data['args'] = self.convert_to_dict(literal_eval(request.data.get('args')))
            if request.data.get('kwargs', False):
                request.data['kwargs'] = self.convert_to_dict(literal_eval(request.data.get('kwargs')))
        except:
            return Response(data={'msg': '“参数”格式错误，请重新输入！'}, status=403)
        return super().update(request, *args, **kwargs)


class WebSiteViewSet(viewsets.ModelViewSet):
    queryset = WebSite.objects.all()
    serializer_class = WebSiteSerializer
    permission_classes = (CustomDjangoModelPermission,)


class PostViewSet(viewsets.ModelViewSet):
    queryset = Post.objects.all()
    serializer_class = PostSerializer
    permission_classes = (CustomDjangoModelPermission,)


class NetworkCardAssetsViewSet(viewsets.ModelViewSet):
    queryset = NetworkCardAssets.objects.all().order_by('id')
    serializer_class = NetworkCardAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class RamAssetsViewSet(viewsets.ModelViewSet):
    queryset = RamAssets.objects.all().order_by('id')
    serializer_class = RamAssetsSerializer
    permission_classes = (CustomDjangoModelPermission,)


class IPSegmentViewSet(viewsets.ModelViewSet):
    queryset = IPSegment.objects.all().order_by('id')
    serializer_class = IPSegmentSerializer
    permission_classes = (CustomDjangoModelPermission,)

    def update(self, request, *args, **kwargs):
        invalid_IP = request.data.get('invalidIP', False)
        instance = self.get_object()
        valid_groups = Assets.objects.filter(asset_management_ip__IP_segment=instance.id).values_list('asset_management_ip__id', flat=True).distinct()
        serializer = self.get_serializer(instance, data=request.data, partial=True)
        serializer.is_valid(raise_exception=True)
        serializer.save()
        IPGroup.objects.filter(IP_segment=instance.id).update(IP_status=1)
        for invalid in invalid_IP:
            if invalid not in valid_groups:
                IPGroup.objects.filter(id=invalid).update(IP_status=0)
        return Response(serializer.data)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if Assets.objects.filter(asset_management_ip__IP_segment=instance.id).count()>0:
            return Response(data={'msg': '存在资产使用该IP网段的地址，请先删除相关资产！'}, status=403)
        else:
            self.perform_destroy(instance)
            return Response(status=204)

    def create(self, request, *args, **kwargs):
        serializer = self.get_serializer(data=request.data)
        IP_invalid = request.data.get('IP_invalid', '')
        segment = request.data.get('segment', '')
        IP_invalid_list = []
        if IP_invalid:
            if not re.compile('^((25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)\,)*(25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)$').search(IP_invalid):
                return Response(data={'IP_invalid': ['无效IP地址格式错误，请重新输入。']}, status=403)
            IP_invalid_list = IP_invalid.split(',')
        if not re.compile('^((25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)\.){2}(25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)$').search(segment):
            return Response(data={'segment': ['IP网段格式错误，请重新输入。']}, status=403)
        serializer.is_valid(raise_exception=True)
        serializer.save()
        instance = IPSegment.objects.get(segment=segment)
        for i in range(1,256):
            status = 0 if str(i) in IP_invalid_list else 1
            IPGroup.objects.create(IP_segment=instance,IP=instance.segment+'.'+str(i),IP_status=status)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=201, headers=headers)


class DBConfigViewSet(viewsets.ModelViewSet):
    queryset = DBConfig.objects.all().order_by('id')
    serializer_class = DBConfigSerializer
    permission_classes = (CustomDjangoModelPermission,)

    def validate_db_server(self, db_server):
        if db_server:
            if Assets.objects.filter(id=db_server, asset_management_ip_id__isnull=True):
                return Response(data={'db_server': ['该资产没有关联IP地址，请重新输入。']}, status=403)

    def create(self, request, *args, **kwargs):
        self.validate_db_server(request.data.get('db_server', False))
        return super().create(request, *args, **kwargs)

    def update(self, request, *args, **kwargs):
        self.validate_db_server(request.data.get('db_server', False))
        return super().update(request, *args, **kwargs)


class DBConfigPagination(PageNumberPagination):
    page_size = 10
    page_size_query_param = 'page_size'
    page_query_param = "page"
    max_page_size = 100

class DBConfigListOrderingFilter(OrderingFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'asset_nu': 'db_server__asset_nu', 'db_IP': 'db_server__asset_management_ip__IP'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class DBConfigListSearchFilter(SearchFilterMixins):
    def filter_queryset(self, request, queryset, view):
        transform = {'asset_nu': 'db_server__asset_nu', 'db_IP': 'db_server__asset_management_ip__IP'}
        return self.custom_filter_queryset(request, queryset, view, **transform)

class DBConfigListViewSet(mixins.ListModelMixin, viewsets.GenericViewSet):
    queryset = DBConfig.objects.all().order_by('id')
    serializer_class = DBConfigListSerializer
    pagination_class = DBConfigPagination
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, DBConfigListSearchFilter, DBConfigListOrderingFilter)
    filter_class = DBConfigListFilter
    search_fields = ('asset_nu', 'db_IP' ,'db_user' )
    ordering_fields = ('id', 'asset_nu', 'db_IP', 'db_type', 'db_user', 'db_name')

class AssetsExportView(GenericAPIView):
    queryset = Assets.objects.all().order_by('id')
    serializer_class = ServerAssetsExportSerializer
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, AssetsListSearchFilter, AssetsListOrderingFilter)
    filter_class = AssetsFilter
    search_fields = ('asset_nu', 'asset_management_ip', 'asset_product')
    ordering_fields = ('id', 'asset_type', 'asset_nu', 'asset_management_ip', 'asset_status', 'is_expired', 'asset_product')

    def post(self, request):
        instance = self.filter_queryset(self.get_queryset())
        serializer = self.get_serializer(instance, many=True)
        data = serializer.data
        convert_data = []
        for asset in data:
            temp = copy.deepcopy(asset)
            asset_management_ip = temp.pop("asset_management_ip")
            temp["IP"] = ""
            temp["port"] = ""
            temp["server_protocol"] = ""
            temp["fort_username"] = ""
            temp["fort_password"] = ""
            temp["fort_user_status"] = ""
            temp["fort_user_memo"] = ""
            if asset_management_ip:
                temp["IP"] = asset_management_ip.pop("IP")
                fortserver_set = "fortserver_set" in asset_management_ip and asset_management_ip.pop("fortserver_set")
                if fortserver_set:
                    for fortserver in fortserver_set:
                        temp["server_protocol"] = fortserver["server_protocol"]
                        temp["port"] = fortserver["port"]
                        fortserveruser_set = "fortserveruser_set" in fortserver and fortserver.pop("fortserveruser_set")
                        if fortserveruser_set:
                            for fortserveruser in fortserveruser_set:
                                temp["fort_username"]=fortserveruser["fort_username"]
                                temp["fort_password"]=fortserveruser["fort_password"]
                                temp["fort_user_status"]=fortserveruser["fort_user_status"]
                                temp["fort_user_memo"]=fortserveruser["fort_user_memo"]
                                convert_data.append(copy.deepcopy(temp))
                        else:
                            convert_data.append(copy.deepcopy(temp))
                else:
                    convert_data.append(temp)
            else:
                convert_data.append(temp)

        tmp_dir = os.path.join(os.path.join(settings.BASE_DIR, 'media'), 'tmp')
        if not os.path.exists(tmp_dir):
            os.mkdir(tmp_dir)
        filepath = os.path.join(tmp_dir, str(uuid.uuid1()).replace('-', '') + '.xlsx')
        workbook = xlsxwriter.Workbook(filepath)
        try:
            worksheet = workbook.add_worksheet(u'服务器资源清单')
            title = workbook.add_format(
                {'bold': True, 'align': 'center', 'valign': 'vcenter', 'bg_color': '#007ee5', 'border': 1, })
            content = workbook.add_format({'text_wrap': True, 'align': 'center', 'valign': 'vcenter', 'border': 1, })
            time = workbook.add_format(
                {'text_wrap': True, 'align': 'center', 'valign': 'vcenter', 'num_format': 'yyyy-mm-dd', 'border': 1, })
            worksheet.set_column(0, 0, 8)
            worksheet.set_column(1, 1, 20)
            worksheet.set_column(2, 2, 15)
            worksheet.set_column(3, 3, 18)
            worksheet.set_column(4, 4, 13)
            worksheet.set_column(5, 5, 13)
            worksheet.set_column(6, 6, 13)
            worksheet.set_column(7, 7, 30)
            worksheet.set_column(8, 8, 15)
            worksheet.set_column(9, 9, 15)
            worksheet.set_column(10, 10, 15)
            worksheet.set_column(11, 11, 15)
            worksheet.set_column(12, 12, 17)
            worksheet.set_column(13, 13, 20)
            worksheet.set_column(14, 14, 10)
            worksheet.set_column(15, 15, 12)
            worksheet.set_column(16, 16, 22)
            worksheet.set_column(17, 17, 22)
            worksheet.set_column(18, 18, 10)
            worksheet.set_column(19, 19, 15)
            row = [u'编号', u'产品名称', u'设备类型', u'宿主机', u'设备状态', u'是否过期', u'设备环境', u'资产名称', u'申请时间', u'过期时间',
                   u'申请人', u'负责人', u'设备备注', u'IP地址', u'连接协议', u'连接端口', u'用户名称', u'用户密码', u'用户状态', u'用户说明']
            for r in range(len(row)):
                worksheet.write(0, r, row[r], title)
            i = 1
            worksheet.freeze_panes(1, 0)
            worksheet.autofilter('A1:T1')
            for server in convert_data:
                worksheet.set_row(i, 30)
                worksheet.write(i, 0, server["id"], content)
                worksheet.write_string(i, 1, server["asset_product"] if server["asset_product"] else '', content)
                worksheet.write_string(i, 2, server["type_detail"] if server["type_detail"] else '', content)
                worksheet.write_string(i, 3, server["hosted_on"] if server["hosted_on"] else '', content)
                worksheet.write(i, 4, server["asset_status"], content)
                worksheet.data_validation(i, 4, i, 4,
                                          {'validate': 'list',
                                           'source': list(dict(Assets.asset_status_).values())})
                worksheet.write(i, 5, server["is_expired"], content)
                worksheet.data_validation(i, 5, i, 5,
                                          {'validate': 'list',
                                           'source': list(dict(is_expired_types).values())})
                worksheet.write(i, 6, server["asset_environment"], content)
                worksheet.data_validation(i, 6, i, 6,
                                          {'validate': 'list',
                                           'source': list(dict(Assets.asset_environment_).values())})
                worksheet.write_string(i, 7, server["asset_nu"] if server["asset_nu"] else '', content)
                worksheet.write_string(i, 8, server["asset_purchase_day"], time)
                worksheet.write_string(i, 9, server["asset_expire_day"], time)
                worksheet.write_string(i, 10, server["asset_applicant"] if server["asset_applicant"] else '', content)
                worksheet.write_string(i, 11, server["asset_runner"] if server["asset_runner"] else '', content)
                worksheet.write_string(i, 12, server["asset_memo"] if server["asset_memo"] else '', content)
                worksheet.write_string(i, 13, server["IP"] if server["IP"] else '', content)
                worksheet.write(i, 14, server["server_protocol"] if server["server_protocol"] else '', content)
                worksheet.data_validation(i, 14, i, 14,
                                          {'validate': 'list',
                                           'source': list(dict(FortServer.server_protocols).values())})
                worksheet.write_string(i, 15, server["port"] if server["port"] else '', content)
                worksheet.write_string(i, 16, server["fort_username"] if server["fort_username"] else '', content)
                worksheet.write_string(i, 17, server["fort_password"] if server["fort_password"] else '', content)
                worksheet.write(i, 18, server["fort_user_status"] if server["fort_user_status"] else '', content)
                worksheet.data_validation(i, 18, i, 18,
                                          {'validate': 'list',
                                           'source': list(dict(FortServerUser.fort_user_status_).values())})
                worksheet.write_string(i, 19, server["fort_user_memo"] if server["fort_user_memo"] else '', content)
                i += 1
        finally:
            workbook.close()

        response = download_response(filepath)
        return response

class DBConfigExportView(GenericAPIView):
    queryset = DBConfig.objects.all().order_by('id')
    serializer_class = DBConfigExportSerializer
    permission_classes = (CustomDjangoModelPermission,)
    filter_backends = (DjangoFilterBackend, DBConfigListSearchFilter, DBConfigListOrderingFilter)
    filter_class = DBConfigListFilter
    search_fields = ('asset_nu', 'db_IP' ,'db_user' )
    ordering_fields = ('id', 'asset_nu', 'db_IP', 'db_type', 'db_user', 'db_name')

    def post(self, request):
        instance = self.filter_queryset(self.get_queryset())
        serializer = self.get_serializer(instance, many=True)
        data = serializer.data
        tmp_dir = os.path.join(os.path.join(settings.BASE_DIR, 'media'), 'tmp')
        if not os.path.exists(tmp_dir):
            os.mkdir(tmp_dir)
        filepath = os.path.join(tmp_dir, str(uuid.uuid1()).replace('-', '') + '.xlsx')
        workbook = xlsxwriter.Workbook(filepath)
        try:
            worksheet = workbook.add_worksheet(u'数据库配置清单')
            title = workbook.add_format(
                {'bold': True, 'align': 'center', 'valign': 'vcenter', 'bg_color': '#007ee5', 'border': 1, })
            content = workbook.add_format({'text_wrap': True, 'align': 'center', 'valign': 'vcenter', 'border': 1, })
            time = workbook.add_format(
                {'text_wrap': True, 'align': 'center', 'valign': 'vcenter', 'num_format': 'yyyy-mm-dd', 'border': 1, })
            worksheet.set_column(0, 0, 8)
            worksheet.set_column(1, 1, 20)
            worksheet.set_column(2, 2, 20)
            worksheet.set_column(3, 3, 10)
            worksheet.set_column(4, 4, 13)
            worksheet.set_column(5, 5, 15)
            worksheet.set_column(6, 6, 13)
            worksheet.set_column(7, 7, 30)
            worksheet.set_column(8, 8, 20)
            worksheet.set_column(9, 9, 20)
            worksheet.set_column(10, 10, 20)
            worksheet.set_column(11, 11, 13)
            worksheet.set_column(12, 12, 20)
            row = [u'编号', u'产品名称', u'资产名称', u'设备环境', u'库类型', u'IP地址', u'数据库端口', u'OracleTNS', u'数据库名称/实例名', u'数据库账号', u'数据库密码', u'数据库编码', u'备注']
            for r in range(len(row)):
                worksheet.write(0, r, row[r], title)
            i = 1
            worksheet.freeze_panes(1, 0)
            worksheet.autofilter('A1:L1')
            for server in data:
                worksheet.set_row(i, 30)
                worksheet.write(i, 0, server["id"], content)
                worksheet.write_string(i, 1, server["db_server"]["asset_product"] if server["db_server"]["asset_product"] else '', content)
                worksheet.write_string(i, 2, server["db_server"]["asset_nu"] if server["db_server"]["asset_nu"] else '', content)
                worksheet.write(i, 3, server["db_server"]["asset_environment"], content)
                worksheet.data_validation(i, 3, i, 3,
                                          {'validate': 'list',
                                           'source': list(dict(Assets.asset_environment_).values())})
                worksheet.write(i, 4, server["type"], content)
                worksheet.data_validation(i, 4, i, 4,
                                          {'validate': 'list',
                                           'source': list(dict(DBConfig.db_type_).values())})
                worksheet.write_string(i, 5, server["db_server"]["db_IP"] if server["db_server"]["db_IP"] else '', content)
                worksheet.write_string(i, 6, server["db_port"] if server["db_port"] else '', content)
                worksheet.write_string(i, 7, server["db_name"] if server["db_oracle_type"] == '1' else '', content)
                worksheet.write_string(i, 8, server["db_name"] if server["db_oracle_type"] == '0' else '', content)
                worksheet.write_string(i, 9, server["db_user"] if server["db_user"] else '', content)
                worksheet.write_string(i, 10, server["db_password"] if server["db_password"] else '', content)
                worksheet.write_string(i, 11, server["db_code"] if server["db_code"] else '', content)
                worksheet.write_string(i, 12, server["db_memo"] if server["db_memo"] else '', content)
                i += 1
        finally:
            workbook.close()

        response = download_response(filepath)
        return response
        # return Response(data)


class ProjectProductViewSet(viewsets.ModelViewSet):
    queryset = ProjectProduct.objects.all().order_by('id')
    serializer_class = ProjectProductSerializer
    permission_classes = (CustomDjangoModelPermission,)


class ProjectViewSet(viewsets.ModelViewSet):
    queryset = Project.objects.all().order_by('id')
    serializer_class = ProjectSerializer
    permission_classes = (CustomDjangoModelPermission,)


class ProjectConfigViewSet(viewsets.ModelViewSet):
    queryset = ProjectConfig.objects.all().order_by('id')
    serializer_class = ProjectConfigSerializer
    permission_classes = (CustomDjangoModelPermission,)


class ServiceViewSet(viewsets.ModelViewSet):
    queryset = Service.objects.all().order_by('id')
    serializer_class = ServiceSerializer
    permission_classes = (CustomDjangoModelPermission,)